package com.kuang.dao.user;

import com.kuang.dao.BaseDao;
import com.kuang.pojo.Role;
import com.kuang.pojo.User;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{

//得到要登录的用户
    public User getLoginUser(Connection connection, String userCode) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;

        if(connection!=null){
            String sql ="select * from smbms_user where userCode=?";
            Object[] params = {userCode};

                rs = BaseDao.execute(connection, pstm, rs, sql, params);
                if(rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getTimestamp("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
                }
                BaseDao.closeResource(null,pstm,rs);



        }
           return user;
}
    //修改当前用户密码
    public int updatePwd(Connection connection,int id,String password) throws  SQLException {
        PreparedStatement pstm = null;
        int execute=0;
        if (connection != null) {
            String sql = "update smbms_user set userPassword=? where id=?";
            Object params[] = {password, id};
          execute = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return execute;
    }
    //根据用户名或者角色查询用户总数【最难的sql】
    public int getUserCount(Connection connection,String userName,int userRole)throws SQLException{
        PreparedStatement pstm=null;
        ResultSet rs=null;
        int count=0;
        if (connection!=null){
            StringBuffer sql=new StringBuffer();
            sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole=r.id");
            ArrayList<Object> list = new ArrayList<>();//存放参数
            if (!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%"+userName+"%");//index:0

            }
            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);//index:1
            }
            //将list转换为数组
            Object[] params = list.toArray();
            System.out.println("UserDaoImpl->getUserCount:"+sql.toString());

            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            if(rs.next()){
              count = rs.getInt("count");

            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }

    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        //使用ArrayList将用户信息封装到里面
        ArrayList<User> users = new ArrayList<User>();
        User user = null;
        if (connection != null){
            //使用list集合去封装参数
            ArrayList<Object> params = new ArrayList<Object>();
            //因为根据用户角色或用户名的条件查询，需要使用StringBuffer去拼接字符串
            StringBuffer sql = new StringBuffer("SELECT * from smbms_user u,smbms_role r where u.userRole = r.id");
            if (!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and userName like ?");
                params.add("%"+userName+"%");
            }
            if (userRole > 0){
                sql.append(" and userRole = ?");
                params.add(userRole);
            }
            //起始下标
            int pageIndex = (currentPageNo - 1) * pageSize;
            sql.append(" order by u.creationDate DESC limit ?,?");
            params.add(pageIndex);
            params.add(pageSize);
            rs = BaseDao.execute(connection, pstm, rs,sql.toString(), params.toArray());
            while (rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
//              user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getDate("modifyDate"));
                user.setUserRoleName(rs.getString("roleName"));
                users.add(user);
            }
            BaseDao.closeResource(null,pstm,rs);
            System.out.println("UserDaoImpl--->getUserCount："+sql.toString());
        }
        return users;
    }


}
